package com.lxu.service;

import com.lxu.entity.StageTablePushConfigEntity;
import com.lxu.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class UserService {

    @Resource(name = "ds1JdbcTemplate")
    private JdbcTemplate jdbcTemplate1;

    @Resource(name = "ds2JdbcTemplate")
    private JdbcTemplate jdbcTemplate2;

    @Resource(name = "ds3JdbcTemplate")
    private JdbcTemplate jdbcTemplate3;

    @Resource(name = "ds4JdbcTemplate")
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Transactional
    public  void  multipleSqlInsert(){
        List<String> sqls = new ArrayList<>();
        String sql5 =  "insert into role(name,description) values('徐良','123')";
        String sql1 = "insert into user(name,passwd,permissions,dept_id) values('徐良','123','333','666')";
        String sql2 = "insert into user(name,passwd,permissions,dept_id) values('001','徐良','123','333','666')";
        String sql3 = "insert into user(name,passwd,permissions,dept_id) values('001','徐良','123','333','666')";
        sqls.add(sql1);
        sqls.add(sql2);
        sqls.add(sql3);
        sqls.add(sql5);
        sqls.forEach(item -> jdbcTemplate1.execute(item));
    }

    public String getUserName() {
        String sqlStr = "select name from user where id=?";
        String userName = jdbcTemplate1.queryForObject(sqlStr, new Object[]{"1"}, String.class);
        System.out.println(userName);
        return userName;
    }

    public User getUser1() {
        String sql = "select id,name from user";
        List<User> users = jdbcTemplate1.query(sql, new RowMapper<User>() {
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getString(1));
                user.setName(rs.getString(2));
                return user;
            }
        });
        return users.get(0);
    }

    public User getUser2() {
        String sql = "select id,name from user";
        List<User> users = jdbcTemplate2.query(sql, new RowMapper<User>() {
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getString(1));
                user.setName(rs.getString(2));
                return user;
            }
        });
        return users.get(0);
    }

    public String getStringTest() {
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("proj_detail_id", "jdproj");
        String reportSql = "SELECT t.template_code FROM report_template_config t WHERE "
                + " t.applicant_type="
                + " (SELECT ps.applicant_type FROM yw_proj_size ps,yw_proj_detail pd,yw_inve_survey_detail isd WHERE ps.id=pd.proj_size_id AND pd.id=isd.proj_detail_id AND pd.id=?)"
                + " AND t.apply_type_code="
                + " (SELECT t2.subject_type FROM yw_inve_survey_detail t1,yw_snaps_cust t2 WHERE t1.proj_detail_id=t2.proj_detail_id AND t1.proj_detail_id=?) "
                + " AND t.template_type_code='JDBG'";

        String templateCode = jdbcTemplate3.queryForObject(reportSql, new Object[]{data.get("proj_detail_id"), data.get("proj_detail_id")}, String.class);
        return templateCode;
    }

    public void mapTest(){
        String querySql = "SELECT count(1) passCount,SUM(t.suggest_money) passMoney FROM yw_inve_survey_detail t WHERE t.inve_survey_id=? AND t.verdict='1' GROUP BY t.inve_survey_id";
        Map<String, Object> result = null;
        try {
            result =jdbcTemplate3.queryForMap(querySql, new Object[]{"123"});
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<StageTablePushConfigEntity> getAllNeedPushTable(String stage) {
        String sql = "SELECT t.dest_table_en,t.table_relation,t.field_relation FROM yw_stage_table_push_config t WHERE t.stage=? ORDER BY t.table_sort ASC";
        return jdbcTemplate3.query(sql, new Object[]{stage}, new RowMapper<StageTablePushConfigEntity>() {
            @Override
            public StageTablePushConfigEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                StageTablePushConfigEntity entity = new StageTablePushConfigEntity();
                entity.setDestTableEn(rs.getString(1));
                entity.setTableRelation(rs.getString(2));
                entity.setFieldRelation(rs.getString(3));
                return entity;
            }
        });
    }
}
